﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using BookingSystemModel;
using System.Data;

namespace BookingSystemDAL
{
    public class UserDAL
    {
        public UserDAL()
        {

        }
        //用户注册
        public   int AddUser(user User)
        {
            int rows = 0;
            string sql = "insert into Usertable(UserName,UserIDNumber,UserNickName,Pwd,Sex,Phone,Email,Address,RegisterTime)" 
                           + 
                              "values(:UserName,:UserIDNumber,:UserNickName,:Pwd,:Sex,:Phone,:Email,:Address,:RegisterTime)";
            OracleParameter[] parameters = {
                                               new OracleParameter(":UserName",User.UserName),
                                               new OracleParameter(":UserIDNumber",User.UserIDNumber),
                                               new OracleParameter(":UserNickName",User.UserNickName),
                                               new OracleParameter(":Pwd",User.Pwd),
                                               new OracleParameter(":Sex",User.Sex),
                                               new OracleParameter(":Phone",User.Phone),
                                               new OracleParameter(":Email",User.Email),
                                               new OracleParameter(":Address",User.Address),
                                               new OracleParameter(":RegisterTime",User.RegisterTime),
                                           };
            try
            {
                rows = OracleHelper.ExecuteNonQuery(sql, parameters);
            }
            catch (System.Exception ex)
            {
                
                throw new Exception(ex.Message);
            }
            return rows;
        }
        //查询所有用户
        public DataSet SearchUser()
        {
            string sql = "select * from Usertable";
            return OracleHelper.ExecuteDataSet(sql);
        }
        //查询用户是否存在
        public DataSet loginUser(string UserNickName, string Pwd)
        {
            string sql = "select *from UserTable where UserNickName=:UserNickName and Pwd=:Pwd";
            OracleParameter[] oracleparameter = 
            {
                new OracleParameter(":UserNickName",UserNickName),
                new OracleParameter(":Pwd",Pwd)
            };
            try
            {
                return OracleHelper.ExecuteDataSet(sql, oracleparameter);
            }
            catch (System.Exception ex)
            {
                throw (ex);
            }
            
        }
        //根据UserNickName查询
        public   DataSet NickNameUser(string UserNickName)
        {
            string sql = "select* from UserTable where UserNickName=" + "'" + UserNickName + "'";
            try
            {
                return OracleHelper.ExecuteDataSet(sql);
            }
            catch (System.Exception ex)
            {
                throw (ex);
            }
        }
        //更新用户
        public int UpdateUserInfo(user UserInfo)
        {
            int rows = 0;
            string sql = "update UserTable set "
                               +"UserName=:UserName,"
                               +"UserIDNumber=:UserIDNumber,"
                               +"UserNickName=:UserNickName,"
                               +"Pwd=:Pwd,"
                               +"Sex=:Sex,"
                                +"Phone=:Phone,"
                                +"Email=:Email,"
                                +"Address=:Address,"
                                +"RegisterTime=:RegisterTime" 
                                +"where UserID=:UserID";
            OracleParameter[] param = 
            {
                new OracleParameter(":UserName",UserInfo.UserName),
                new OracleParameter(":UserIDNumber",UserInfo.UserIDNumber),
                new OracleParameter(":UserNickName",UserInfo.UserNickName),
                new OracleParameter(":Pwd",UserInfo.Pwd),
                new OracleParameter(":Sex",UserInfo.Sex),
                new OracleParameter(":Phone",UserInfo.Phone),
                new OracleParameter(":Email",UserInfo.Email),
                new OracleParameter(":Address",UserInfo.Address),
                new OracleParameter(":RegisterTime",UserInfo.RegisterTime)
            };
            try
            {
                rows = OracleHelper.ExecuteNonQuery(sql, param);
            }
            catch (System.Exception ex)
            {
                throw (ex);
            }
            return rows;

        }
        //用户更改密码
         public  int UpdatePwd(string Pwd ,string UserNickName)
        {
            string sql = "update UserTable set" + "Pwd=:Pwd" + "where UserNickName=:UserNickName";
            OracleParameter[] param = { new OracleParameter(":Pwd", Pwd) };
             try
             {
                 return OracleHelper.ExecuteNonQuery(sql, param);
             }
             catch (System.Exception ex)
             {
                 throw (ex);
             }
        }
    }
}
